package com.neu.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.neu.dao.StudentDao;
import com.neu.entity.Student;
import com.neu.util.JDBCUtil;

public class StudentDaoImpl implements StudentDao {
/**
 *   查询全部
 */
	@Override
	public List<Student> findAll() throws ClassNotFoundException, SQLException{
//			Connection conn = JDBCUtil.getConn();
//			String sql = "select*from student";
//			PreparedStatement ps = conn.prepareStatement(sql);
//			List<Student> list = new ArrayList<>();
//			ResultSet rs = ps.executeQuery();
//			while(rs.next()) {
//				list.add(new Student(rs.getInt(1),rs.getString(2) ,rs.getString(3) ,rs.getShort(4) ));
//			}
//		return list;
//		
	
		
		//获取Connection对象，通过工具类
		Connection conn=JDBCUtil.getConn();
		//声明sql语句
		String sql="select * from student";
		//生成执行器
		PreparedStatement ps=conn.prepareStatement(sql);
		//生成结果集，对结果进行处理
		ResultSet rs=ps.executeQuery();
		//声明List集合，将结果集中的数据存放到集合中
		List<Student> list_stu=new ArrayList<>();
		//循环结果集，完成取值
		while(rs.next()) {
			//列下标1对应的是表中的id字段
			list_stu.add(new Student(rs.getInt(1), rs.getString(2),
					rs.getString(3), rs.getInt(4)));
		}
		return list_stu;
	}


	/**
	 * 查询单一信息的学生   
	 * @throws ClassNotFoundException 
	 */
	@Override
	public Student findOneStudent(int id) throws SQLException, ClassNotFoundException {
//	public Student findOneStudent(Student stu) throws SQLException, ClassNotFoundException {
//			Connection conn = JDBCUtil.getConn();
//			//   如果你定义的是 int id 参数  name你 sql 语句直接写一个条件就行
////			String sql = "select*from student where id=?";
//		//   如果你定义的是 Student stu 参数  name你 sql 语句直接可以加入或者来查询
//			String sql = "select*from student where id=? or name=?";
//			PreparedStatement ps = conn.prepareStatement(sql);
//			ps.setInt(1, stu.getId());
//			ps.setString(2, stu.getName());
//			ResultSet rs = ps.executeQuery();
//			if(rs.next()) {
//				stu= new Student(rs.getInt(1),rs.getString(2),rs.getString(3) ,rs.getInt(4));
//			}
//		return null;
		
		
	//  生成  Connection 对象
			Connection conn = JDBCUtil.getConn();
			//  声明   mysql  的语句
			String sql = "select * from student where id=?";
			//  生成执行器
			PreparedStatement ps = conn.prepareStatement(sql);
			// 赋值   是根据 编号来查询学生的信息   1: 代表的是你的   mysql 语句中第一个问号的下标    \
			ps.setInt(1, id);
			//  你要执行你的查询:
			ResultSet rs = ps.executeQuery();
			Student st = null;
			if(rs.next()) {
				st=new Student(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4));
			}
			return st;
	}
			/**
			 * 更新学生信息
			 * @throws ClassNotFoundException 
			 * 
			 */
	@Override
	public boolean updateStudent(Student stu) throws SQLException, ClassNotFoundException{
//		Connection conn = JDBCUtil.getConn();
//		String sql = "update student set name=?,city=?,age=? where id=?";
//		PreparedStatement ps = conn.prepareStatement(sql);
//		ps.setString(1, stu.getName());
//		ps.setString(2, stu.getCity());
//		ps.setInt(3, stu.getAge());
//		ps.setInt(4, stu.getAge());
//		int a = ps.executeUpdate();
//		if(a>0) {
//			return true;
//		}
//		return false;
//		
		

//		
		Connection conn = JDBCUtil.getConn();
		String sql = "update student set name=?,city=?,age=? where id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1,stu.getName());
		System.out.println(stu.getName());
		ps.setString(2, stu.getCity());
		ps.setInt(3, stu.getAge());
		ps.setInt(4,stu.getId());
		int a = ps.executeUpdate();
		if(a>0) {
			return true;
		}
		return false;
	}
	/**
	 *   删除学生信息
	 * @throws ClassNotFoundException 
	 *   
	 */
	@Override
	public boolean deleteStudentById(int id) throws SQLException, ClassNotFoundException {
		//获取Connection对象，通过工具类  
		//   JDBCUtil  
		 boolean flag = false;
		Connection conn = JDBCUtil.getConn();
		String sql="select * from studentcource where student_id=?";
		//生成执行器
		PreparedStatement ps=conn.prepareStatement(sql);
		//生成结果集，对结果进行处理
		ps.setInt(1,id);
		ResultSet rs = ps.executeQuery();
		if(rs.next()) {
		String sql_fk="delete from studentcource where student_id=?";
		PreparedStatement ps1=conn.prepareStatement(sql_fk);
		ps1.setInt(1,id);
		int a=ps1.executeUpdate();
		if(a>0) {
			String sql_pk ="delete from student where id=?";
			PreparedStatement ps2=conn.prepareStatement(sql_pk);
			ps2.setInt(1,id);
			int a_pk=ps1.executeUpdate();
			if(a_pk>0) {
				System.out.println("删除成功");
				flag= true;
			}
		}
	}else {
		String sql_pk ="delete from student where id=?";
		PreparedStatement ps3=conn.prepareStatement(sql_pk);
		ps3.setInt(1,id);
		int a_pk=ps3.executeUpdate();
		if(a_pk>0) {
			System.out.println("删除成功");
	}
		
}
		return flag;
	}
	/**
	 *    增加数据   
	 * @throws ClassNotFoundException 
	 * 
	 */
	@Override
	public boolean addStudent(Student stu) throws SQLException, ClassNotFoundException {
		Connection conn = JDBCUtil.getConn();
		String sql = "insert into student value(null,?,?,?)";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setString(1, stu.getName());
		ps.setString(2, stu.getCity());
		ps.setInt(3, stu.getAge());
		int a = ps.executeUpdate();
		if(a>0) {
			return true;
		}
		return false;
	}

}

